package com.kitri.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.kitri.db.SetOfDataSource;
import com.kitri.domain.Course;
import com.kitri.domain.Member;

public class CourseDaoImpl implements CourseDao {
	@Override
	public Course select(String courseId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM COURSE WHERE COURSE_ID=?");

		final Course course = new Course();

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		jdbcTemplate.query(sql.toString(), new Object[] { courseId },
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						// mapping
						course.setCourseId(rs.getString("course_id"));
						course.setCourseName(rs.getString("course_name"));
						course.setClassRoom(rs.getString("class_room"));
						if (rs.getString("start_date") != null) {
							course.setStartDate(rs.getString("start_date")
									.substring(0, 10));
						}
						if (rs.getString("start_date") != null) {
							course.setEndDate(rs.getString("end_date")
									.substring(0, 10));
						}
						course.setSubjectList(((SubjectDaoImpl) AppContext
								.getAppContext().getBean("subjectDao"))
								.selectOfCourse(rs.getString("course_id")));
					}
				});

		return course;
	}

	@Override
	public List<Course> selectAll() {
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM COURSE");
		sql.append(" ORDER BY COURSE_NAME");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		List<Course> list = new ArrayList<Course>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql
				.toString());

		for (Map<String, Object> row : rows) {
			Course course = new Course();

			course.setCourseId((String) row.get("course_id"));
			course.setCourseName((String) row.get("course_name"));
			course.setClassRoom((String) row.get("class_room"));

			if (row.get("start_date") != null) {
				course.setStartDate(sf.format((Date) row.get("start_date"))
						.substring(0, 10));
			}
			if (row.get("end_date") != null) {
				course.setEndDate(sf.format((Date) row.get("end_date"))
						.substring(0, 10));
			}
			course.setSubjectList(((SubjectDaoImpl) AppContext.getAppContext()
					.getBean("subjectDao")).selectOfCourse((String) row
					.get("course_id")));

			list.add(course);
		}

		return list;
	}

	@Override
	public boolean insert(Course course) {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO MEMBER (");
		sql.append("COURSE_ID, ");
		sql.append("COURSE_NAME, ");
		sql.append("CLASS_ROOM, ");
		sql.append("START_DATE, ");
		sql.append("END_DATE");
		sql.append(") VALUES(");
		sql.append("concat(substr('C0000000', 1, 8-length(to_char(course_seq.nextval))), to_char(course_seq.nextval)), ");
		sql.append("?, ?, to_date(?, 'YYYY-MM-DD'),  to_date(?, 'YYYY-MM-DD'))");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { course.getCourseName(), course.getClassRoom(),
						course.getStartDate(), course.getEndDate() });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean update(Course course) {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE COURSE SET ");
		sql.append("COURSE_NAME = ?, ");
		sql.append("CLASS_ROOM = ?, ");
		sql.append("START_DATE = to_date(?, 'YYYY-MM-DD'), ");
		sql.append("END_DATE = to_date(?, 'YYYY-MM-DD') ");
		sql.append("WHERE COURSE_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(
				sql.toString(),
				new Object[] { course.getCourseName(), course.getClassRoom(),
						course.getStartDate(), course.getEndDate(),
						course.getCourseId() });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public boolean delete(String courseId) {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM COURSE ");
		sql.append("WHERE COURSE_ID = ?");

		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());

		int result = jdbcTemplate.update(sql.toString(),
				new Object[] { courseId });

		if (result > 0)
			return true;
		return false;
	}

	@Override
	public List<Member> selectOfMember(String courseId) {
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
		SimpleDateFormat gsf = new SimpleDateFormat("yyyy");
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM Juction_Mb_Co, member ");
		sql.append("WHERE  Juction_Mb_Co.seq_id = member.seq_id ");
		sql.append("AND Juction_Mb_Co.course_id = ?");
		
		JdbcTemplate jdbcTemplate = new JdbcTemplate(
				SetOfDataSource.getDataSource());
		List<Member> list = new ArrayList<Member>();
		List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql
				.toString(), new Object[] { courseId });

		for (Map<String, Object> row : rows) {

			Member member = new Member();

			member.setSeqId((String) row.get("seq_id"));
			member.setMemberId((String) row.get("member_id"));
			member.setMemberPassword((String) row.get("member_password"));
			member.setName((String) row.get("name"));
			member.setGender((String) row.get("gender"));
			member.setDateOfBirth(sf.format((Date) row.get("date_of_birth")));
			member.setPhoneNumber((String) row.get("phone_number"));
			member.setEmail((String) row.get("email"));
			member.setUniversity((String) row.get("university"));
			member.setMajor((String) row.get("major"));
			if (row.get("graduated_year") != null) {
				member.setGraduatedYear(gsf.format((Date) row
						.get("graduated_year")));
			}
			member.setAddress((String) row.get("address"));
			member.setAddPoint((String) row.get("add_point"));
			member.setLanguage((String) row.get("language"));
			member.setCourse(((CourseDao) AppContext.getAppContext().getBean(
					"courseDao")).select((String) row.get("course_id")));
			member.setCertificationList(((CertificationDaoImpl) AppContext
					.getAppContext().getBean("certificationDao"))
					.selectOfCertification((String) row.get("seq_id")));

			list.add(member);
		}

		return list;
	}
}
